package Test1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;

public class JDBCUile {
	private static ResourceBundle rb = ResourceBundle.getBundle("Test1.jdbc");
	private static String classname = rb.getString("driver");
	private static String url = rb.getString("url");
	private static String username = rb.getString("username");
	private static String password = rb.getString("password");

	static {
		try {
			Class.forName(classname);
			System.out.println("驱动加载成功");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("驱动加载失败");
			e.printStackTrace();
		}

	}

	public static void exClose(ResultSet resultSet, Statement statement, Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	// 获得连接
	public static Connection getConnection() {
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(url, username, password);
			System.out.println("数据库连接成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("数据库连接失败");
			e.printStackTrace();
		}
		return connection;

	}
	// 插入 单个

	public void insertSingle(Student student) {

		String sql = "insert into students values(?,?,?,?,?,?)";
		Connection connection = getConnection();

		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		Integer id = student.getId();
		String name = student.getName();
		int age = student.getAge();
		String sex = student.getSex();
		String classGrade = student.getClass_grade();
		String major = student.getMajor();

		int r = 0;
		try {
			ps.setInt(1, id);
			ps.setString(2, name);
			ps.setInt(3, age);
			ps.setString(4, sex);
			ps.setString(5, classGrade);
			ps.setString(6, major);
			r = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(r);

		exClose(null, ps, connection);

	}

	public static int[] insertList(List<Student> studentList) throws SQLException {

		Connection connection = getConnection();

		String sql = "insert into students valuse(?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			connection.setAutoCommit(false);
			ps = connection.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		for (int i = 0; i < studentList.size(); i++) {

			Student student = studentList.get(i);
			int id = student.getId();
			String name = student.getName();
			int age = student.getAge();
			String sex = student.getSex();
			String classGrade = student.getClass_grade();
			String major = student.getMajor();

			try {
				ps.setInt(1, id);
				ps.setString(2, name);
				ps.setInt(3, age);
				ps.setString(4, sex);
				ps.setString(5, classGrade);
				ps.setString(6, major);
				ps.addBatch();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		int[] re = ps.executeBatch();
		try {
			connection.commit();
			ps.clearBatch();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		exClose(null, ps, connection);

		return re;
	}

	// 删除
	public static void delte(Student student) {

		Connection connection = getConnection();
		String sql = "delete from students where id=?";
		PreparedStatement ps = null;

		try {
			ps = connection.prepareStatement(sql);
			int id = student.getId();
			ps.setInt(1, id);
			ps.execute();

			System.out.println("删除成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(null, ps, connection);

	}

	public static List<Object> appendWhere(StringBuffer sql, Student student) {
		List<Object> list = new ArrayList<>();
		Integer id = student.getId();
		if (id != 0) {
			sql.append("and id = ? ");
			list.add(id);

		}
		String name = student.getName();
		if (name != null && !name.trim().isEmpty()) {
			sql.append("and name = ? ");
			list.add(name);
		}
		int age = student.getAge();
		if (age != 0) {
			sql.append("and age = ? ");
			list.add(age);
		}
		String sex = student.getSex();
		if (sex != null && !sex.trim().isEmpty()) {
			sql.append("and sex = ? ");
			list.add(sex);

		}
		String classGrade = student.getClass_grade();
		if (classGrade != null && !classGrade.trim().isEmpty()) {
			sql.append("and class_grade = ? ");
			list.add(classGrade);

		}
		String major = student.getMajor();
		if (major != null && !major.trim().isEmpty()) {
			sql.append("and major = ? ");
			list.add(major);

		}

		return list;

	}

	// 按照条件查询学生信息
	public static ArrayList<Student> selectSingle(Student student) throws Exception {
		StringBuffer sql = new StringBuffer("select ");
		sql.append("id,name,age,sex,class_grade,major from students where 1=1 ");
		List<Object> list = appendWhere(sql, student);

		Connection connection = null;

		ResultSet rs = null;
		connection = getConnection();

		ArrayList<Student> students = new ArrayList<>();

		PreparedStatement ps = null;

		ps = connection.prepareStatement(sql.toString());

		for (int i = 0; i < list.size(); i++) {
			ps.setObject(i + 1, list.get(i));
		}

		rs = ps.executeQuery();

		while (rs.next()) {
			Student student2 = new Student();
			student2.setId(rs.getInt("id"));
			student2.setName(rs.getString("name"));
			student2.setAge(rs.getInt("age"));
			student2.setClass_grade(rs.getString("class_grade"));
			student2.setMajor(rs.getString("major"));
			students.add(student2);

		}

		exClose(rs, ps, connection);

		return students;

	}
	// 查询所有学生的信息

	public static ArrayList<Student> selectAll() throws Exception {
		String sql = "select * from students";

		Connection connection = null;

		ResultSet rs = null;
		connection = getConnection();

		ArrayList<Student> students = new ArrayList<>();

		PreparedStatement ps = null;

		connection.setAutoCommit(false);
		ps = connection.prepareStatement(sql.toString());

		rs = ps.executeQuery();
		connection.commit();

		while (rs.next()) {
			Student student2 = new Student();
			student2.setId(rs.getInt("id"));
			student2.setName(rs.getString("name"));
			student2.setAge(rs.getInt("age"));
			student2.setSex(rs.getString("sex"));
			student2.setClass_grade(rs.getString("class_grade"));
			student2.setMajor(rs.getString("major"));

			students.add(student2);

		}

		exClose(rs, ps, connection);

		return students;

	}

	// 更新信息
	public static void updateInformation(String Name, String Values, int id) {
		Connection connection = getConnection();

		String sql = "update students set " + Name + "= '" + Values + "' where id =" + id;
		System.out.println(sql);
		Statement statement = null;
		try {
			statement = connection.createStatement();
			statement.execute(sql);

			System.out.println("操作成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("操作失败");
			e.printStackTrace();
		}

		exClose(null, statement, connection);

	}

}
